Progress So Far

So far, you’ve:

• Signed up for GCP
• Created a project
• Added BigQuery to that project
• Looked at public data in another project
• Used that data to create a query and a table in your project

Let’s Export!

::: notes One way you can work with the data that you’ve groomed and selected from BigQuery is by exporting it to use elsewhere. Depending on the size of the data and how often the data changes, this might be a great use case, and very convenient. Let’s go over that briefly.

Now, you might think that the Export button as you’re looking at a table would be a great way to get data out of GCP, but this export button is actually for exporting to other Google products, like Looker Studio or Google Cloud Storage. If that’s what you want, fantastic, but let’s assume you want to take a .csv and just run with that data somewhere else. What should you do?

To export a .csv, you need to have query results, and you can save those in various formats. So we have a couple of options.

:::

Getting Query Results to Save

Since you’ve saved the query you used to create this table, you can re-run that query.

Or, since you’ve created a table that contains all the data you want, you run a “give me everything” query:

SELECT * FROM [table_name]

Saving Data Exercise

R vs RStudio

Quick reminder: R is a language, that can be run in many settings:

• from the command line
• in an automation (like a cron job)
• from the R IDE (very bare bones)
• from the RStudio IDE
• within a notebook like a Jupyter notebook

RStudio is a fully featured IDE that runs on Linux, Windows, and Mac. It’s much more heavy weight than just the language.

Working with RStudio IDE

Many of us love RStudio. You can spin up an RStudio server in GCP… but you don’t have to, and it might mean spending money when you don’t need to.

You can use your normal RStudio (on your computer or in Posit.cloud) to work with BigQuery.

Following Along

Visit https://github.com/pm0kjp/rmedicine_2024_bigquery/blob/main/bq_demo.qmd and get the code, but we’ll also do this together bit by bit.

Using non-GCP RStudio

bigrquery

There are several ways to connect to BigQuery (see, e.g. https://solutions.posit.co/connections/db/databases/big-query/) but the way I’m going to show you uses a library called bigrquery.

Exercise

Please install bigrquery as well as tidyverse, if you don’t already have that installed.

Then you’re going to start a new Quarto or R Markdown document and add a code chunk that loads these two libraries:

Run a Query from RStudio

The pattern is:

• Set your project_id and my_sql_query objects
• Send that query to BigQuery using results <- bq_project_query(project_id, my_sql_query)
• Get the table via df <- bq_table_download(results)

Take a look!

Visit https://github.com/pm0kjp/rmedicine_2024_bigquery/blob/main/bq_demo.qmd and get the code, but we’ll also do this together bit by bit.

Setting up R kernel

In a new Terminal in your Jupyter instance, enter the following:

conda create -n r
conda config --add channels conda-forge
conda install -c conda-forge r-base
conda install -c conda-forge r-essentials
conda install -c conda-forge r-tidyverse
conda install -c conda-forge r-stringr
conda install -c conda-forge r-gargle
conda install -c conda-forge r-bigrquery
conda activate r

Answer “y” when prompted.